package com.itany.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Author:shixiaojun@itany.com
 * Date:2022/11/11 9:28
 * Version:1.0-SNAPSHOT
 * Description: SQL注入
 */
public class Test03 {

    public static void main(String[] args) {
//        List<User> users = selectByUsernameAndPassword("1","1");
//        List<User> users = selectByUsernameAndPassword("1' or '1'='1","1' or '1'='1");
        List<User> users = selectByUsernameAndPassword2("1' or '1'='1","1' or '1'='1");
        System.out.println(users);
        for(User user : users){
            System.out.println(user);
        }
    }

    public static List<User> selectByUsernameAndPassword(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        List<User> users = new ArrayList<>();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            st = conn.createStatement();
            String sql = new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .append(" where username = '"+username+"' ")
                    .append(" and password = '"+password+"' ")
                    .toString();
            rs = st.executeQuery(sql);
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                users.add(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }

    public static List<User> selectByUsernameAndPassword2(String username,String password){
        List<User> users = new ArrayList<>();

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc?useUnicode=true&characterEncoding=utf-8","root","");
            String sql = new StringBuffer()
                    .append(" select id,username,password,phone,address ")
                    .append(" from t_user ")
                    .append(" where username = ? ")
                    .append(" and password= ?")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            ps.setString(2,password);
            rs = ps.executeQuery();
            while(rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setPhone(rs.getString("phone"));
                user.setAddress(rs.getString("address"));
                users.add(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return users;
    }
}
